//
//  SqliteManager.swift
//  MicroBlog
//
//  Created by Apple on 16/10/9.
//  Copyright © 2016年 Apple. All rights reserved.
//

import UIKit
//创建数据库路径
let dbPath = (NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).last! as NSString).appendingPathComponent("sinaWeibo.db")
//数据库专用类
class SqliteManager: NSObject {
    //单例全局访问点
    static let sharedManager: SqliteManager = SqliteManager()
    //数据操作队列对象
    lazy var queue: FMDatabaseQueue = FMDatabaseQueue(path: dbPath)
    //构造函数私有化
    private override init() {
        super.init()
        createTables()
    }
    
    //创建表
    private func createTables() {
        //SQL语句
        let path = Bundle.main.path(forResource: "db.sql", ofType: nil)!
        let sql = try! String(contentsOfFile: path)
        //执行sql
        queue.inDatabase { (db) in
            let result = db?.executeStatements(sql)
            if result == true {
                print("创建表成功")
            } else {
                print("创建表失败")
            }
        }
    }
    
    //插入数据
    func insert() {
        //SQL
        let sql = "INSERT INTO T_PERSON(NAME,AGE) VALUES(?,?)"
        //执行SQL
        queue.inDatabase { (db) in
            let result = db?.executeUpdate(sql, withArgumentsIn: ["jack",18])
            if result!{
                print("插入成功")
            } else {
                print("插入失败")
            }
        }
    }
    
    //查询数据
    func select() {
        //SQL
        let sql = "SELECT ID, NAME, AGE FROM T_PERSON"
        //执行SQL
        queue.inDatabase { (db) in
            if let resultSet = db?.executeQuery(sql, withArgumentsIn: nil) {
                //当有下一条记录说明查询结果不为空
                while resultSet.next() {
                    let id = resultSet.int(forColumn: "ID")
                    let name = resultSet.string(forColumn: "NAME")
                    let age = resultSet.int(forColumn: "AGE")
                    print("id: \(id), name: \(name), age: \(age)")
                }
            }
        }
    }
    
    //通过查询返回数组字典
    func selectDicArray(sql: String) -> [[String: Any]] {
        //存储字典
        var tempArray = [[String: Any]]()
        //执行sql
        queue.inDatabase { (db) in
            if let resultSet = db?.executeQuery(sql, withArgumentsIn: nil){
                //遍历结果集
                while resultSet.next() {
                    var dic = [String: Any]()
                    //获取列数
                    let colCount = resultSet.columnCount()
                    //遍历列数
                    for i in 0..<colCount {
                        //获取列名
                        let colName = resultSet.columnName(for: i)!
                        //获取列值
                        let colValue = resultSet.object(forColumnIndex: i)!
                        //添加键值对
//                        dic.updateValue(colValue, forKey: colName)
                        dic[colName] = colValue
                    }
                    tempArray.append(dic)
                }
            }
        }
        return tempArray
    }
    
    //修改操作
    func update() {
        //sql
        let sql = "UPDATE T_PERSON SET NAME = ?, AGE = ? WHERE ID = ?"
        //执行sql
        queue.inDatabase { (db) in
            let result = db?.executeUpdate(sql, withArgumentsIn: ["rose", 14, 1])
            if result! {
                print("修改成功")
            } else {
                print("修改失败")
            }
        }
    }
    
    //删除
    func delete() {
        //sql
        let sql = "DELETE FROM T_PERSON WHERE ID = 2"
        //执行sql
        queue.inDatabase { (db) in
            let result = db?.executeUpdate(sql, withArgumentsIn: nil)
            if result == true {
                print("删除成功")
            } else {
                print("删除失败")
            }
        }
    }
    
}
